package com.blog.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Vector;
import com.blog.dto.User;
import com.blog.util.DataAccess;
import com.blog.util.DateTime;

public class UserDao {
	
	/**
	 * 通过用户名和用户密码查找用户
	 * @param _username
	 * @param _password
	 * @return superstate //用户权限
	 * @throws SQLException 
	 */
	public int findUserByUsernameAndPassword(String _username , String _password){
		int superstate = 0;
		PreparedStatement ps=null;
		Connection con = null;
		ResultSet rs = null;
		String sql = "select superstate from user where username = ? and password = ?  and ( delete_at  is  NULL ) ";
		con = DataAccess.getInstance().getConnection();
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _username);
			ps.setString(2, _password);
			rs = ps.executeQuery();
			if(rs.next())
			superstate = Integer.parseInt(rs.getString("superstate"));
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return superstate;
	}
	
	/**
	 * 传入用户id，返回一条记录
	 * @param _id
	 * @return
	 */
	public Vector<User> findUserById(int _id) {
		Vector<User> v=new Vector<User>();
		User user = new User();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from user where user_id = ?  and ( delete_at  is  NULL )  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			rs = ps.executeQuery();
			if(rs.next()) {
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			} 
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 传入用户名，查询用户信息，返回一条记录
	 * @param _username
	 * @return
	 */
	public Vector<User> findUserByUsername(String _username) {
		Vector<User> v=new Vector<User>();
		User user = new User();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from user where username LIKE ? and ( delete_at  is  NULL ) ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+_username+"%");
			rs = ps.executeQuery();
			if(rs.next()) {
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	public Vector<User> findUserByNameAndTime(String mintime , String maxtime ,String _username) {
		Vector<User> v=new Vector<User>();
		User user = new User();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
//		select * from t1 where unix_timestamp(time1) > unix_timestamp('2011-03-03 17:39:05') and unix_timestamp(time1) < unix_
//		timestamp('2011-03-03 17:39:52');
//		就是用unix_timestamp函数，将字符型的时间，转成unix时间戳。个人觉得这样比较更踏实点儿。
		String sql = " select * from user where username LIKE ? and unix_timestamp(add_time) > unix_timestamp(?) and unix_timestamp(add_time) < unix_timestamp(?) and ( delete_at  is  NULL ) ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+_username+"%");
			ps.setTimestamp(2, DateTime.StrToDate(mintime));
			ps.setTimestamp(3, DateTime.StrToDate(maxtime));
			rs = ps.executeQuery();
			if(rs.next()) {
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	
	/**
	 * 查找所有用户信息
	 * @return 存有用户信息的vector容器
	 */
	public Vector<User> findAllUser() {
		Vector<User> v=new Vector<User>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from user where delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据权限查找所有用户信息
	 * @return 存有用户信息的vector容器
	 */
	public Vector<User> findUserBySuper(int _super) {
		Vector<User> v=new Vector<User>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from user where delete_at  is  NULL And superstate = ? ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _super);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	public Vector<User> findAdmin() {
		Vector<User> v=new Vector<User>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from user where delete_at  is  NULL And superstate > ? ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, 1);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据用户名查找用户id
	 * @param _name
	 * @return
	 */
	public int findUserIdByName( String _name ) {
		int user_id = 0 ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select user_id from user where username = ? and delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, _name);
			rs = ps.executeQuery();
			if (rs.next()) user_id=rs.getInt("user_id");
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return user_id;
	}
	
	/**
	 * 查找被软删除的用户
	 * @param _name
	 * @return
	 */
	public Vector<User> findDelUser() {
		Vector<User> v=new Vector<User>();
		Connection con = DataAccess.getInstance().getConnection();
		ResultSet rs = null;
		try {
			CallableStatement proc = con.prepareCall("{ call query_delUser() }");
			rs = proc.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setAdd_time(rs.getTimestamp("add_time"));
				user.setEmail(rs.getString("email"));
				user.setLast_ip(rs.getString("last_ip"));
				user.setLast_login(rs.getTimestamp("last_login"));
				user.setPassword(rs.getString("password"));
				user.setSuperstate(rs.getInt("superstate"));
				user.setUser_id(rs.getInt("user_id"));
				user.setUsername(rs.getString("username"));
				v.add(user);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con, rs );
		}
		return v;
	}
	
	
	/**
	 * 向User表插入一条记录
	 * @param u
	 * @return
	 */
	public boolean addUser(User u) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into user values(null,?,?,?,?,?,?,?,?)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, u.getUsername());
			ps.setString( 2 , u.getPassword());
			ps.setString( 3 , u.getEmail());
			ps.setInt( 4 , u.getSuperstate());
			ps.setTimestamp( 5, u.getAdd_time());
			ps.setTimestamp(6, u.getLast_login());
			ps.setString(7, u.getLast_ip());
			ps.setTimestamp(8, u.getDelete_at());
			if(ps.executeUpdate() == 1) flag=true;
			
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		
		return flag;
	}
	
	/**
	 * 修改用户信息
	 * @param u
	 * @return
	 */
	public boolean editUser(User u) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  user set username = ? , password = ? , email = ? , superstate = ?  , last_ip = ?  " + 
				"  where user_id = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, u.getUsername());
			ps.setString( 2 , u.getPassword());
			ps.setString( 3 , u.getEmail());
			ps.setInt( 4 , u.getSuperstate());
//			ps.setTimestamp(5, u.getLast_login());
			ps.setString(5, u.getLast_ip());	
			ps.setInt(6, u.getUser_id());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!修改失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;	
	}
	
	/**
	 * 修改用户的最后登录的时间和最后一次登录的ip
	 * @param _id
	 * @param _ip
	 * @param _logintime
	 * @return
	 */
	public boolean updateUserLastIp(int _id , String _ip ,Timestamp _logintime) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  user set  last_login = ? , last_ip = ?  " + 
				"  where user_id = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, _logintime);
			ps.setString(2, _ip);
			ps.setInt(3, _id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!修改登录时间和ip失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;	
	}
	
	/**
	 * 根据用户id删除用户
	 * 使用软删除，将delete_at字段设置为删除的时间
	 * @param _id
	 * @return
	 */
	public boolean delUserById (int _id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  user set  delete_at = ?  " + 
				"  where user_id = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, _id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据用户id删除用户
	 * 使用硬删除，删除用户相关的所有东西
	 * @param _id
	 * @return
	 */
	public boolean hardDelUserById (int _id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " delete from user " + 
				"  where user_id = ? AND delete_at is not null ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, _id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!硬删除失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据用户名删除用户
	 * @param _username
	 * @return
	 */
	public boolean delUserByUsername (String _username) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  user set  delete_at = ?  " + 
				"  where username = ? and (delete_at is null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setString(2, _username);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软删除失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 通过用户名对进行了软删除的用户进行恢复
	 * @param _username
	 * @return
	 */
	public boolean restoreUserByUsername(String _username) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  user set  delete_at = ?  " + 
				"  where username = ? and (delete_at is not null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, null);
			ps.setString(2, _username);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软恢复失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 通过用户id对进行了软删除的用户进行恢复
	 * @param _id
	 * @return
	 */
	public boolean restoreUserById(int _id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = " update  user set  delete_at = ?  " + 
				"  where user_id = ? and (delete_at is not null) ;";
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, null);
			ps.setInt(2, _id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!软恢复失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	
	
	
	
	
	
}

